# _*_ coding:utf-8 _*_
# @File  : ruizy_variety.py
# @Time  : 2022-02-09  10:58
# @Author: zizle

# 品种
import datetime
import time
from typing import List
from fastapi import APIRouter, Query, Body
from fastapi.encoders import jsonable_encoder
from pydantic import BaseModel, ValidationError, validator, constr, conint, datetime_parse

from db import FAConnection
from v1_all_api import constant
from v1_all_api.all_utils import datetime_utils
from v1_all_api.all_response import AllResponse

variety_api = APIRouter()


# 品种的空间名称处理器
def variety_zh_name_handler(item):
    item['variety_long_name'] = f'{item["variety_name"]}({item["variety_en"]})'
    item['spaces_zh'] = ','.join([constant.VARIETY_SPACES.get(s, '') for s in item['spaces'].split(',')])
    return item


class VarietyItem(BaseModel):
    variety_name: constr(min_length=1)
    variety_en: constr(min_length=1)
    group_name: constr(min_length=1)
    exchange_name: constr(min_length=1)
    spaces: str
    is_active: bool
    is_real: bool
    price_unit: str
    hand_unit: str
    hand_amount: float
    fund: float
    bail_rate: float

    @validator('spaces')
    def validate_spaces(cls, value):
        for space in value.split(','):
            if space not in constant.VARIETY_SPACES.keys():
                raise ValidationError('品种空间错误')
        return value


@variety_api.post('/variety/')  # 添加或更新品种数据
def create_variety(variety_item: VarietyItem = Body(...)):
    if variety_item.group_name not in ['农业产品', '能源化工', '金属期货', '金融期货']:
        return AllResponse.validate_error(msg='品种分组错误!')
    if variety_item.exchange_name not in ['中金所', '大商所', '郑商所', '上期所', '上能源', '广期所', '非交易所']:
        return AllResponse.validate_error(msg='品种交易所错误!')
    variety_spaces = variety_item.spaces.split(',')
    variety_spaces.sort()
    variety_item.spaces = ','.join(variety_spaces)
    db_conn = FAConnection()
    # 查询是否存在
    exist_sql = 'SELECT id FROM ruizy_variety WHERE variety_en=%s;'
    exist_prm = [variety_item.variety_en]
    is_exist = db_conn.query(exist_sql, exist_prm, fetchone=True, keep_conn=True)[0]
    if is_exist:
        op_sql = """
            UPDATE ruizy_variety SET variety_name=%s,group_name=%s,exchange_name=%s,
            is_active=%s,is_real=%s,spaces=%s,price_unit=%s,hand_unit=%s,hand_amount=%s,fund=%s,bail_rate=%s 
            WHERE variety_en=%s;
        """
        op_prm = [variety_item.variety_name, variety_item.group_name, variety_item.exchange_name,
                  variety_item.is_active, variety_item.is_real, variety_item.spaces, variety_item.price_unit,
                  variety_item.hand_unit, variety_item.hand_amount, variety_item.fund, variety_item.bail_rate,
                  variety_item.variety_en]
        op_message = '更新品种信息成功!'
    else:
        op_sql = """
            INSERT INTO ruizy_variety (variety_en,spaces,variety_name,group_name,exchange_name,is_active,is_real,
            price_unit,hand_unit,hand_amount,fund,bail_rate) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);
        """
        op_prm = [variety_item.variety_en, variety_item.spaces, variety_item.variety_name, variety_item.group_name,
                  variety_item.exchange_name, variety_item.is_active, variety_item.is_real, variety_item.price_unit,
                  variety_item.hand_unit, variety_item.hand_amount, variety_item.fund, variety_item.bail_rate]
        op_message = '创建品种成功!'
    _, success = db_conn.execute(op_sql, op_prm)
    return AllResponse.create_successfully(msg=op_message) if success else AllResponse.server_error()


@variety_api.get('/variety/')  # 获取系统品种数据
def ruizy_variety(space: str = Query(None)):
    # sql = """
    #     SELECT v.id,v.variety_en,v.spaces,v.variety_name,v.group_name,v.exchange_name,v.is_real,
    #         v.price_unit,v.hand_unit,v.hand_amount,v.bail_rate,v.fund,
    #         c.lcontract,c.scontract
    #     FROM ruizy_variety As v
    #     LEFT JOIN
    #     (SELECT variety_en,lcontract,scontract,activity FROM ruizy_variety_contract
    #     WHERE contract_date=(SELECT MAX(contract_date) FROM ruizy_variety_contract)
    #     ) As c ON v.variety_en=c.variety_en AND c.activity=0;
    # """
    # db_conn = FAConnection()
    # variety = db_conn.query(sql)

    with FAConnection() as cursor:
        cursor.execute("SELECT id,variety_en,spaces,variety_name,group_name,exchange_name,is_real,price_unit,hand_unit,hand_amount,bail_rate,fund FROM ruizy_variety;")
        variety_list = list(cursor.fetchall())
        cursor.execute("""
            SELECT variety_en,lcontract,scontract,activity FROM ruizy_variety_contract
            WHERE activity=0 AND contract_date=(SELECT MAX(contract_date) FROM ruizy_variety_contract);
        """)
        contract_list = list(cursor.fetchall())
    for v in variety_list:
        v['lcontract'] = ''
        v['scontract'] = ''
        for c in contract_list:
            if v['variety_en'] == c['variety_en']:
                v['lcontract'] = c['lcontract']
                v['scontract'] = c['scontract']
                break
    variety = variety_list
    if space:
        variety = list(filter(lambda x: space in x['spaces'].split(','), variety))
    variety = list(map(variety_zh_name_handler, variety))
    if space and space == 'V06':
        variety.sort(key=lambda x: (x['variety_en'], x['is_real']))
    else:
        variety.sort(key=lambda x: (x['is_real'], x['variety_en']))
    return AllResponse.operate_successfully(data=variety)


class VarietyContract(BaseModel):
    contract_date: datetime_parse.date
    variety_en: constr(min_length=1)
    ex_code: constr(min_length=1)
    lcontract: constr(min_length=1)
    scontract: constr(min_length=1)
    activity: conint(ge=0)


@variety_api.post('/variety/updateContract/')  # 更新品种合约
def update_variety_contract(contract_list: List[VarietyContract] = Body(...)):
    db_conn = FAConnection()
    maintain_date = contract_list[0].contract_date.strftime('%Y%m%d')
    exist_sql = "SELECT id FROM ruizy_variety_contract WHERE contract_date=%s;"  # 查询本日数据是否存在
    exist = db_conn.query(exist_sql, param=[maintain_date], fetchone=True, keep_conn=True)[0]
    if exist:
        db_conn.close()
        return AllResponse.no_do_anything(msg='今日合约数据已存在，无需更新!')

    insert_sql = """
        INSERT INTO ruizy_variety_contract (contract_date,variety_en,ex_code,lcontract,scontract,activity) VALUES 
        (%(contract_date)s,%(variety_en)s,%(ex_code)s,%(lcontract)s,%(scontract)s,%(activity)s);
    """
    for c_item in contract_list:
        c_item.contract_date = c_item.contract_date.strftime('%Y%m%d')

    contracts = jsonable_encoder(contract_list)
    count, _ = db_conn.insert(insert_sql, param=contracts, many=True)
    return AllResponse.operate_successfully(msg='添加合约成功,数量：{}'.format(count))


def get_special_contract():
    contracts = []
    now = datetime.datetime.now()
    contracts.append(now.strftime('%Y%m'))

    month_days = 30
    if now.month in [1, 3, 5, 7, 8, 10, 12]:
        month_days = 31
    next_month = now+datetime.timedelta(days=month_days)
    contracts.append(next_month.strftime('%Y%m'))

    next_month_days = 30
    if next_month.month in [1, 3, 5, 7, 8, 10, 12]:
        next_month_days = 31
    next_month2 = now + datetime.timedelta(days=month_days + next_month_days)
    contracts.append(next_month2.strftime('%Y%m'))

    contracts.append((now + datetime.timedelta(days=month_days+next_month_days+30)).strftime('%Y%m'))  # 第3个月

    # 两个季度月
    contracts.append((now + datetime.timedelta(days=6*30)).strftime('%Y%m'))
    contracts.append((now + datetime.timedelta(days=9*30)).strftime('%Y%m'))
    contracts.append((now + datetime.timedelta(days=12*31)).strftime('%Y%m'))
    return contracts


@variety_api.get('/variety/contract/')  # 获取品种的合约
def get_variety_contract(v: str = Query(None), m: int = Query(0, ge=0, le=1), date: str = Query(None)):
    if date:
        date = datetime_utils.auth_datetime_string(datetime_string=date, f='%Y%m%d')
    if not date:
        date = datetime.datetime.today().strftime('%Y%m%d')
    if v and v in constant.SPECIAL_VARIETY.keys():  # 特殊指定的合约。如外汇
        # 返回当前月和后连续2月和连续3个季月的合约
        contract_list = get_special_contract()
        rep_data = []
        for c in contract_list:
            c = c[2:]
            rep_data.append({
                'variety_en': v,
                'ex_code': v,
                'lcontract': c,
                'scontract': c,
                'activity': 1,
                'contract_date': date,
                'exchange_name': '',
                'price_unit': 1,
                'hand_amount': 1,
                'hand_unit': 1,
                'fund': 300000,
                'bail_rate': 0.18,
                'mix_contract': c,
                'variety_name': constant.SPECIAL_VARIETY.get(v)
            })
        return AllResponse.operate_successfully(data=rep_data)
    db_conn = FAConnection()
    variety_en = v
    is_query_activity = m
    # 日期的合约
    query_ = datetime.datetime.strptime(date, '%Y%m%d') + datetime.timedelta(days=-1)
    query_date = datetime_utils.exchange_date_nearby(date_node=query_, f='%Y%m%d')
    sql = """
        SELECT a.id,a.variety_en,a.ex_code,a.lcontract,a.scontract,a.activity,a.contract_date,
            b.exchange_name,b.price_unit,b.hand_amount,b.hand_unit,b.fund,b.bail_rate,b.variety_name 
        FROM ruizy_variety_contract  As a 
        INNER JOIN ruizy_variety As b ON a.variety_en=b.variety_en 
        WHERE a.contract_date=%s;
    """
    prm = [query_date]
    records = db_conn.query(sql, param=prm)
    if variety_en:  # 指定品种、
        records = list(filter(lambda x: x['variety_en'] == variety_en, records))
    if is_query_activity:  # 查询的是主力合约 activity=0
        records = list(filter(lambda x: x['activity'] == 0, records))
    records.sort(key=lambda x: x['scontract'])
    for c in records:
        c['mix_contract'] = f'{c["variety_en"]}{c["lcontract"]}'
    return AllResponse.operate_successfully(data=records)
